/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.model.service;

import com.model.domain.ubicacion.Departamento;
import com.model.domain.ubicacion.Distrito;
import com.model.domain.ubicacion.Provincia;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


/**
 *
 * @author Rdelacruz
 */
public class DistritoService 
extends AbstractService
{
    
    public List<Distrito> findByDepartamentoProvincia(Connection con, String codigoDepartamento, String codigoProvincia) throws SQLException {
        List<Distrito> lista = new ArrayList<Distrito>();
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select * from Distrito where ccdd='"+codigoDepartamento+"' and ccpp='"+codigoProvincia+"'";
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()) {
                Departamento dep = new Departamento();
                dep.setCodigo(rs.getString("ccdd"));
                
                Provincia prov = new Provincia();
                prov.setCodigo(rs.getString("ccpp"));
                prov.setDepartamento(dep);
                
                Distrito d = new Distrito();
                d.setCodigo(rs.getString("ccdi"));
                d.setNombre(rs.getString("nombre_dist").toUpperCase());
                d.setProvincia(prov);
                
                lista.add(d);
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return lista;
    }

    public List<Distrito> findAll(Connection con) throws SQLException {
        List<Distrito> lista = new ArrayList<Distrito>();
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select ccdd, ccpp, ccdi, nombre_dist from Distrito";
            ResultSet rs = st.executeQuery(sql);            
            while(rs.next()) {
                Departamento dep = new Departamento();
                dep.setCodigo(rs.getString(1));
                
                Provincia prov = new Provincia();
                prov.setCodigo(rs.getString(2));
                prov.setDepartamento(dep);
                
                Distrito obj = new Distrito();
                obj.setCodigo(rs.getString(3));
                obj.setNombre(rs.getString(4).toUpperCase());
                lista.add(obj);
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return lista;
    }

    public Distrito findByUbigeo(Connection con, String ubigeo) throws SQLException {
        if(ubigeo==null)
            return null;
        
        Statement st = null;
        try {                        
            st = con.createStatement();
            String cdep = ubigeo.substring(0, 2);
            String cprov = ubigeo.substring(2, 4);
            String cdist = ubigeo.substring(4, 6);
            
            String sql = "select * from Distrito where"
                    + " ccdd='"+cdep+"' and"
                    + " ccpp='"+cprov+"' and"
                    + " ccdi='"+cdist+"'";
            ResultSet rs = st.executeQuery(sql);            
            if(rs.next()) {
                Departamento dep = new Departamento();
                dep.setCodigo(rs.getString("ccdd"));
                
                Provincia prov = new Provincia();
                prov.setCodigo(rs.getString("ccpp"));
                prov.setDepartamento(dep);
                
                Distrito obj = new Distrito();
                obj.setCodigo(rs.getString("ccdi"));
                obj.setNombre(rs.getString("nombre_dist").toUpperCase());
                return obj;
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return null;
    }
    
    
}
